Set ANSI_NULLS ON
Set QUOTED_IDENTIFIER ON
GO

/*Drop stored procedures if they exists */

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'crearUsuario' and type = 'P')

BEGIN
    DROP PROCEDURE crearUsuario
END

GO

/*Done droping stored procedures*/


CREATE PROCEDURE crearUsuario ( 
				@nombreRealUsuario VARCHAR (40) ,
				@apellidoPaterno VARCHAR(40) ,
				@apellidoMaterno VARCHAR (40) ,
				@telefono VARCHAR (40) ,
				@mailContacto VARCHAR(40) , 
				@idRol INT , 
				@outParam INT OUTPUT,
				@outNomUs VARCHAR (30) OUTPUT,
				@outPassUs VARCHAR (30) OUTPUT						
		              )
AS
	DECLARE @out AS INT;
	DECLARE @ident AS INT;
	DECLARE @user AS VARCHAR(30);
	DECLARE @pass AS VARCHAR(30);
	
	SET @ident = 0;
	SET @out = -1;
		
	BEGIN TRANSACTION;
	
		BEGIN TRY
			
			SELECT @ident = MAX(IDUSUARIO) FROM USUARIO;

			SET @ident = @ident + 1;
			
			SET @user = LOWER(@nombreRealUsuario) + '_' +  LOWER(SUBSTRING(@apellidoPaterno,0,3)) + LOWER(CAST(@ident AS VARCHAR(10))) ;

			SET @pass = LOWER(SUBSTRING(@nombreRealUsuario,0,3)) + LOWER(SUBSTRING(@apellidoPaterno,0,3)) + LOWER(SUBSTRING(@apellidoMaterno,0,3));
			
			INSERT INTO USUARIO (NOMBREUSUARIO , PASSWORDUSUARIO , NOMBREREALUSUARIO , APELLIDOPATERNOUSUARIO , APELLIDOMATERNOUSUARIO , TELEFONOCONTACTOUSUARIO , CORREOCONTACTOUSUARIO , IDESTADOUSUARIO , IDROL ) 
			             VALUES (@user , @pass , @nombreRealUsuario ,  @apellidoPaterno , @apellidoMaterno , @telefono , @mailContacto , 1 , @idRol);
			
			SET @out = 1;
			
			SET @outParam = @out;
			
			SET @outNomUs = @user;
			SET @outPassUs = @pass;
			
			COMMIT TRANSACTION;
			 
			
		
		END TRY
		
		BEGIN CATCH
		
			SET @outParam = @out;
			
			ROLLBACK TRANSACTION;
		
		END CATCH;